-------------------------------------------------------------
-- @name: subquery_transformation
-- @author: dion cho
-- @description: test for subquery transformation
--------------------------------------------------------------

@off_cbqt

drop table t1 purge;
drop table t2 purge;
drop table t3 purge;
drop table t1 ;
drop table t2 ;
drop table t3 ;

create table t1(c1 int, c2 char(10), c3 int);
create table t2(c1 int, c2 char(10), c3 int);
create table t3(c1 int, c2 char(10), c3 int);

create index t1_n1 on t1(c1);
create index t2_n1 on t2(c1);
create index t3_n1 on t3(c1);

insert into t1 
select level, 'dummy', mod(level, 10)+1 from dual
connect by level <= 10000
;


insert into t2
select level, 'dummy', mod(level, 10)+1 from dual
connect by level <= 1000
;

insert into t3
select level, 'dummy', mod(level, 10)+1 from dual
connect by level <= 100
;

commit;

@gather t1
@gather t2
@gather t3


-------------------------------------------
-- subquery unnesting

-- subquery unnesting for "in"

-- when nested. filter operation being used
select count(*) 
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select /*+ no_unnest */ t2.c1 from t2)
)
;

@stat


-- when unnested. hash semi join being used
select count(*) 
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
)
;

@stat


-- is filter that evil?
-- Almost yes, but 
-- following has same reads
select /*+ gather_plan_statistics */ 
  max(c1) 
from t1 
where c2 = 'dummy'
;

@stat

select /*+ gather_plan_statistics */ 
  max(c1) 
from t1 
where c3 = 1
;

@stat

-- but filter is efficient as following
select /*+ gather_plan_statistics */
  *
from t1
where c1 = (select /*+ no_unnest */ max(c1) 
            from t1 x
            where x.c3 = t1.c3
            )
;

@stat


-- even more efficent
select /*+ gather_plan_statistics */
  *
from t1
where c1 = (select /*+ no_unnest */ max(c1) 
            from t1 x
            where x.c2 = t1.c2
            )
;

@stat



-- When only first rows are required
select * from (
select /*+ gather_plan_statistics  */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2)
) 
where rownum <= 1;

@stat

-- first rows requirement is solved by "filter" operation?
-- yes, why not?
select * from (
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select /*+ no_unnest */ t2.c1 from t2)
) 
where rownum <= 1;

@stat


-- nested loop semi join
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select t2.c1 from t2) and
  t1.c1 = 1
;

@stat



-- remove aggregate subquery
select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','true') */
   t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;

@stat


select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','false') */
  t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) from t2 s where s.c1 = t1.c1)
;

@stat


select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','true') */
  t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select /*+ no_unnest */ max(c2) from t2 s where s.c1 = t1.c1)
;

@stat

-- subquery unnesting for "not in"(anti join)
-- check this on 11g(null aware anti join)
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 not in (select t2.c1 from t2)
;

@stat

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 not in (select t2.c1 from t2 where t2.c1 is not null) and
  t1.c1 is not null
;

@stat


select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 not in (select /*+ no_unnest */ t2.c1 from t2 
              where t2.c1 is not null) and
  t1.c1 is not null
;

@stat


-- subquery unnested but "not" semi/anti join
-- note that subquery unnesting goes with view merging!!!
select count(*)
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select rownum from t2)
)
;

@stat


select count(*)
from
(
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select /*+ no_unnest */ rownum from t2)
)
;

@stat

select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select max(c1) from t2)
;

@stat


select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select c1 from t2 
            union all
            select c1 from t3)
;

@stat


select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
  t1.c1 in (select c1 from t2 
            union all
            select c1 from t3)
  and t1.c1 between 1 and 100
;

@stat


-- unnested but inefficient query
select /*+ gather_plan_statistics */
 t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2 where c2 = 'dummy' 
        or c1 between 1 and 100) 
;

@stat

-- any solution? rewrite the query
select /*+ gather_plan_statistics */
 t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2 where c2 = 'dummy')
union
select 
  t1.c1, t1.c2 
from
  t1
where
  c1 between 1 and 100
;

@stat



-- "non" unnestable subquery
-- or. solution = ?
select /*+ gather_plan_statistics */
 t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2) or
   t1.c1 between 1 and 100  
;

@stat


-- solution for non-unnestable "or" query
-- rewrite query
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2)
union
select 
  t1.c1, t1.c2
from
  t1
where
  t1.c1 between 1 and 100
;

@stat


-- or 
select /*+ gather_plan_statistics  */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (select c1 from t2 where t2.c1 not between 1 and 100)
union all
select 
  t1.c1, t1.c2
from
  t1
where
  t1.c1 between 1 and 100
;

@stat


-- complex subqueries and unnesting
-- complex subquery #1
select /*+ gather_plan_statistics */
 t1.c1, t1.c2
from
  t1
where
   t1.c1 in (
    select c1 
    from t2
    where exists (
        select 1 
        from t3 
        where 
            t3.c1 = t2.c1 
        ) 
  )
;

@stat


-- complex subquery #2
-- this not unnestable. any solution?
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (
    select c1 
    from t2
    where exists (
        select 1 
        from t3 
        where 
            t3.c1 = t1.c1
        ) 
  )
;

@stat


-- rewrite the query. think unnest as "join"
select /*+ gather_plan_statistics */
  t1.c1, t1.c2
from
  t1
where
   t1.c1 in (
    select c1 
    from t2) 
   and exists (
        select 1
        from t3
        where t3.c1 = t1.c1
      )
;

@stat


-----------------------------------------
-- aggregate subquery elimination
select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','false') */
  t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) 
    from t2 s where s.c1 = t1.c1)
;

@stat

select /*+ gather_plan_statistics 
          opt_param('_remove_aggr_subquery','true') */
   t1.c1, t2.c2
from 
  t1, t2
where
  t1.c1 = t2.c1 and 
  t2.c2 = (select max(c2) 
    from t2 s where s.c1 = t1.c1)
;

@stat_all


----------------------------------------
-- subquery pushing 

select /*+ gather_plan_statistics */
  t1.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1 and
  t2.c2 in (select /*+ no_unnest no_push_subq */ c2 from t3)
;

@stat


select /*+ gather_plan_statistics */
  t1.c1, t2.c2
from
  t1, t2
where
  t1.c1 = t2.c1 and
  t2.c2 in (select /*+ no_unnest push_subq */ c2 from t3)
;

@stat
